package WebPAC::Input::Excel;

use warnings;
use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw/int2col/;
use base qw/WebPAC::Common/;

=head1 NAME

WebPAC::Input::Excel - support for Microsoft Excell and compatibile files

=head1 VERSION

Version 0.04

=cut

our $VERSION = '0.04';


=head1 SYNOPSIS

Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
or Gnuemeric) in C<.xls> format.

=head1 FUNCTIONS

=head2 new

Returns handle to database and size

  my $excel = new WebPAC::Input::Excel(
  	path => '/path/to/workbook.xls'
	worksheet => 'name of sheet',
	from => 42,
	to => 9999,
  }

C<worksheet> is case and white-space insensitive name of worksheet in Excel
file to use. If not specified, it will use first worksheet in file.

C<from> and C<to> specify row numbers to start and finish import.

=cut

sub new {
	my $class = shift;
	my $self = {@_};
	bless($self, $class);

	my $log = $self->_get_logger();

	$log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path});

	my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path});

	my $sheet;
	my $wanted_worksheet;

	if ($wanted_worksheet = $self->{worksheet}) {
		my $name;
		do {
			$sheet = shift @{ $workbook->{Worksheet} };
			$log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet));
			$name = $sheet->{Name};
			$name =~ s/\s\s+/ /g;
		} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);

	} else {

		$sheet = shift @{ $workbook->{Worksheet} };
	
	}

	$self->{sheet} = $sheet;

	$self->{from} ||= $sheet->{MinRow};
	$self->{to} ||= $sheet->{MaxRow};

	my $size = $self->{to} - $self->{from};
	$self->{size} = $size;

	$log->warn("opening Excel file '$self->{path}', using ",
		$wanted_worksheet ? '' : 'first ',
		"worksheet: $sheet->{Name} [$size rows]"
	);

	$self ? return $self : return undef;
}

=head2 fetch_rec

Return record with ID C<$mfn> from database

  my $rec = $self->fetch_rec( $mfn );

}

=cut

sub fetch_rec {
	my $self = shift;

	my $mfn = shift;

	my $log = $self->_get_logger();

	my $sheet = $self->{sheet};
	$log->logdie("can't find sheet hash") unless (defined($sheet));
	$log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));

	my $rec;

	my $row = $self->{from} + $mfn - 1;

	$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});

	foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
		if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) {
			$rec->{ int2col($col) } = $v;
		}
	}

	# add mfn only to records with data
	$rec->{'000'} = [ $mfn ] if ($rec);
	
	return $rec;
}

=head2 size

Return number of records in database

  my $size = $isis->size;

=cut

sub size {
	my $self = shift;
	return $self->{size};
}
=head1 AUTHOR

Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>

=head1 COPYRIGHT & LICENSE

Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=cut

1; # End of WebPAC::Input::Excel
